package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.dao.MemberCardDAO;
import com.junzhiit.nengji.shop.model.MemberCard;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.MemberCardStatus;
import com.junzhiit.nengji.shop.model.enums.MemberCardType;
import com.junzhiit.nengji.shop.model.enums.ShopClientStatus;

@Repository
public class MemberCardDAOImpl extends AbstractBaseDAO<MemberCard> implements MemberCardDAO{

	@Override
	public List<MemberCard> findAll(String csId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from member_card where cs_id=? and state =? and is_delete =?");
		sql.append(" order by created_time desc");
		StatementParameter param = new StatementParameter();
		param.setString(csId);
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		return queryForList(sql.toString(), MemberCard.class, param);
	}

	@Override
	public List<MemberCard> findMemberCardName(String cdId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select id,card_name,type from member_card where cs_id = ? and state = ? and is_delete =?");

		StatementParameter param = new StatementParameter();
		param.setString(cdId);
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		return queryForList(sql.toString(), MemberCard.class, param);
	}

	@Override
	public MemberCard getMaxGrade(String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select * from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ?");
		sql.append(" order by card_grade desc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public MemberCard getMinConsumePrice(Double consumePrice, String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select consume_price from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ? and consume_price < ?");
		sql.append(" order by consume_price desc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		param.setDouble(consumePrice);
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public MemberCard getMaxConsumePrice(Double consumePrice, String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select consume_price from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ? and consume_price > ?");
		sql.append(" order by consume_price asc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		param.setDouble(consumePrice);
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public MemberCard getMinBuyCount(Integer buyCount, String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select buy_count from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ? and buy_count < ?");
		sql.append(" order by buy_count desc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		param.setInt(buyCount);
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public MemberCard getMaxBuyCount(Integer buyCount, String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select buy_count from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ? and buy_count > ?");
		sql.append(" order by buy_count asc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		param.setInt(buyCount);
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public MemberCard getMinConsumeIntegral(Integer consumeIntegral, String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select consume_integral from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ? and consume_integral < ?");
		sql.append(" order by consume_integral desc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		param.setInt(consumeIntegral);
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public MemberCard getMaxConsumeIntegral(Integer consumeIntegral, String cdId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select consume_integral from member_card where state = ?");
		sql.append(" and type = ? and is_delete = ? and cs_id = ? and consume_integral > ?");
		sql.append(" order by consume_integral asc limit 0,1");
		param.setInt(MemberCardStatus.NORMAL.toIntStatus());
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setString(cdId);
		param.setInt(consumeIntegral);
		return super.query(sql.toString(),MemberCard.class,param);
	}

	@Override
	public List<MemberCard> findAllByShopId(List<String> csIds, String userId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		
		sql.append(" select mc.* from shop_client sc left join member_card mc on sc.member_card_id = mc.id  ");
		sql.append(" where ( sc.member_end_date is null  or sc.member_end_date > now()) ");
		sql.append(" and sc.client_status = ? ");
		sql.append(" and ( mc.is_present_coupon = ? or mc.is_present_coupon is null ) ");
		sql.append(" and ( mc.coupons_id is null or mc.coupons_id = '' ) ");
		sql.append(" and sc.user_id = ? ");
		param.setInt(ShopClientStatus.MEMBER.toIntStatus());
		param.setInt(Is.NO.toIntValue());
		param.setString(userId);
		if(csIds!=null){
			sql.append(" and sc.cs_id in ( ");
			for(String s:csIds){
				sql.append("?,");
				param.setString(s);
			}
			sql.deleteCharAt(sql.length()-1);
			sql.append(")");
		}
		
		return super.queryForList(sql.toString(), MemberCard.class, param);
	}

	@Override
	public List<MemberCard> findAllavAilable(String csId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select id,card_name,open_time,type,valid_time_state,valid_start_time,card_grade,");
		sql.append("valid_end_time,instruction,valid_days,price from member_card ");
		sql.append(" where cs_id=? and type != ? and is_delete = ? ");
		sql.append(" order by created_time desc");
		StatementParameter param = new StatementParameter();
		param.setString(csId);
		param.setInt(MemberCardType.ONE_LEVEL.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());

		return queryForList(sql.toString(), MemberCard.class, param);
	}

	@Override
	public MemberCard findAllByCsId(String csIds, String userId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		
		sql.append(" select mc.* from shop_client sc left join member_card mc on sc.member_card_id = mc.id  ");
		sql.append(" where ( sc.member_end_date is null  or sc.member_end_date > now()) ");
		sql.append(" and sc.client_status = ? ");
		sql.append(" and (( mc.is_free_carriage = ? or mc.is_present_coupon is not null ) or ( mc.is_discount = ? or mc.is_discount is not null ) ) ");
		sql.append(" and sc.user_id = ? and sc.cs_id = ? ");
		param.setInt(ShopClientStatus.MEMBER.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setString(userId);
		param.setString(csIds);
		
		return super.query(sql.toString(), MemberCard.class, param);
	}

	@Override
	public List<MemberCard> findActiveCard() {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select * from member_card where is_delete = ? and state = ?");
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		
		return queryForList(sql.toString(), MemberCard.class, param);
	}

}
